package Dao;

import Pojo.CreditCard;
import Utils.DBUtil;
import Utils.isString;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class CreditCardDao {
    private final DBUtil dbUtil = new DBUtil();
    private final Logger logger = Logger.getLogger(CreditCardDao.class);
    private final isString isString = new isString();

    /**
     * 插入信用卡信息
     *
     * @param cardNumId
     * @param cardPrice
     * @param cardTime
     * @param bankId
     * @param userId
     * @return
     * @throws SQLException
     */
    public int insertCreditCardByUserId(String cardNumId,
                                        String cardPrice,
                                        String cardTime,
                                        String bankId,
                                        int userId) throws SQLException {
        String sql = "insert into `creditcard`(" +
                "cardNumId," +
                "cardPrice," +
                "cardTime," +
                "bankId," +
                "userId" +
                ") " +
                "values(?,?,?,?,?)";
        Object[] params = {cardNumId, cardPrice, cardTime, bankId, userId};
        logger.info("sql=>" + sql);
        logger.info("params=>" + Arrays.toString(params));
        return dbUtil.update(sql, params);
    }

    /**
     * 获取未还款的-30~30天内的信用卡
     *
     * @param userId
     * @param pageIndex
     * @return
     */
    public List<Map<String, Object>> getCreditCardNoRePayByUserIdAndIndex(int userId, String cardNum, int pageIndex) throws SQLException {
        String sql = "select" +
                " cardId," +
                "cardNumId," +
                "cardPrice," +
                "DATE_FORMAT(cardTime,'%Y-%m-%d') cardTime," +
                "bankName from creditcard cd " +
                "LEFT JOIN bank bk ON cd.bankId=bk.bankId " +
                "where " +
                "userId= ? " +
                "and " +
                "cardNumId like ? " +
                "AND " +
                "cardTime BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND ADDDATE(CURDATE(), INTERVAL 30 DAY) " +
                "AND " +
                "cardState = 1 " +
                "order by cardTime asc " +
                "LIMIT ?,10";
        Object[] params = {userId, cardNum, pageIndex};
        logger.info("sql=>" + sql);
        logger.info("params=>" + Arrays.toString(params));
        return dbUtil.queryCustoms(sql, params);
    }

    /**
     * 获取未还款账户的总数
     *
     * @param userId
     * @param cardNum
     * @return
     * @throws SQLException
     */
    public int getCreditCardNoRePayByUserIdAndIndex(int userId, String cardNum) throws SQLException {
        String sql = "select" +
                " count(cardId) " +
                "from creditcard " +
                "where " +
                "userId= ? " +
                "and " +
                "cardNumId like ? " +
                "AND " +
                "cardTime BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND ADDDATE(CURDATE(), INTERVAL 30 DAY) " +
                "AND " +
                "cardState = 1";
        Object[] params = {userId, cardNum};
        logger.info("sql=>" + sql);
        logger.info("params=>" + Arrays.toString(params));
        return dbUtil.queryCount(sql, params);
    }

    /**
     * 修改信用卡还款状态
     *
     * @param userId
     * @param cardId
     * @return
     * @throws SQLException
     */
    public int updateCardStateByUserIdAndCardId(int userId, String cardId) throws SQLException {
        String sql = "update `creditcard` set cardState=2 where userId = ? and cardId = ? and cardState=1";
        Object[] params = {userId, cardId};
        return dbUtil.update(sql, params);
    }

    /**
     * 条件查询且分页
     *
     * @param minTime
     * @param maxTime
     * @param bankId
     * @param isPay
     * @param search
     * @return
     */
    public List<Map<String, Object>> getManagerCardBySeletive(int userId,
                                                              String minTime,
                                                              String maxTime,
                                                              String bankId,
                                                              String isPay,
                                                              String search,
                                                              int pageIndex) throws SQLException {
        String sql = "SELECT " +
                "cardId, " +
                "cardNumId, " +
                "cardPrice, " +
                "DATE_FORMAT(cardTime,'%Y-%m-%d') cardTime, " +
                "bk.bankName, " +
                "cardState " +
                "FROM creditcard cd " +
                "LEFT JOIN bank bk on cd.bankId=bk.bankId " +
                "WHERE " +
                "userId = ? ";
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        List<Object> params = new ArrayList<>();
        params.add(userId);
        if (!isString.IsEmpty(minTime, maxTime)) {
            sb.append("and cardTime BETWEEN ? AND ? ");
            params.add(params.size(), new SimpleDateFormat("yyyy-MM-dd").format(new Date(minTime)));
            params.add(params.size(), new SimpleDateFormat("yyyy-MM-dd").format(new Date(maxTime)));
        }
        if (!isString.IsEmpty(bankId)) {
            sb.append("and cd.bankId = ? ");
            params.add(params.size(), bankId);
        }
        if (!isString.IsEmpty(isPay)) {
            sb.append("and cardState = ? ");
            params.add(params.size(), isPay);
        }
        if (!isString.IsEmpty(search)) {
            search = "%" + search + "%";
            sb.append("and cardNumId like ? ");
            params.add(params.size(), search);
        }
        sb.append("ORDER BY cardTime asc ");
        sb.append("LIMIT ?,10");
        params.add(params.size(), pageIndex);
        logger.info("sql=>" + sql);
        logger.info("params=>" + Arrays.toString(params.toArray()));
        return dbUtil.queryCustoms(sb.toString(), params.toArray());
    }

    /**
     * 条件查询返回总数
     *
     * @param userId
     * @param minTime
     * @param maxTime
     * @param bankId
     * @param isPay
     * @param search
     * @return
     * @throws SQLException
     */
    public int getManagerCardCountBySeletive(int userId,
                                             String minTime,
                                             String maxTime,
                                             String bankId,
                                             String isPay,
                                             String search) throws SQLException {
        String sql = "SELECT " +
                "count(cardId) " +
                "FROM creditcard cd " +
                "WHERE " +
                "userId = ? ";
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        List<Object> params = new ArrayList<>();
        params.add(userId);
        if (!isString.IsEmpty(minTime, maxTime)) {
            sb.append("and cardTime BETWEEN ? AND ? ");
            params.add(params.size(), new SimpleDateFormat("yyyy-MM-dd").format(new Date(minTime)));
            params.add(params.size(), new SimpleDateFormat("yyyy-MM-dd").format(new Date(maxTime)));
        }
        if (!isString.IsEmpty(bankId)) {
            sb.append("and cd.bankId = ? ");
            params.add(params.size(), bankId);
        }
        if (!isString.IsEmpty(isPay)) {
            sb.append("and cardState = ? ");
            params.add(params.size(), isPay);
        }
        if (!isString.IsEmpty(search)) {
            search = "%" + search + "%";
            sb.append("and cardNumId like ? ");
            params.add(params.size(), search);
        }
        logger.info("sql=>" + sql);
        logger.info("params=>" + Arrays.toString(params.toArray()));
        return dbUtil.queryCount(sb.toString(), params.toArray());
    }

    /**
     * 获取单个信用卡的信息
     *
     * @param userId
     * @param cardId
     * @return
     * @throws SQLException
     */
    public Map<String, Object> getCreditCardByUserAndId(int userId, String cardId) throws SQLException {
        String sql = "select " +
                "cardId," +
                "cardNumId," +
                "cardPrice," +
                "DATE_FORMAT(cardTime,'%Y-%m-%d') cardTime," +
                "bankId," +
                "cardState " +
                "from `creditcard` " +
                "where userId = ? " +
                "and " +
                "cardId = ? ";
        Object[] params = {userId, cardId};
        return dbUtil.queryCustom(sql, params);
    }

    /**
     * 修改信用卡信息
     *
     * @param cardPrice
     * @param cardTime
     * @param cardState
     * @param bankId
     * @param userId
     * @param cardId
     * @return
     * @throws SQLException
     */
    public int updateCreditCardMsgByUserIDAndCardId(String cardPrice,
                                                    String cardTime,
                                                    String cardState,
                                                    String bankId,
                                                    int userId,
                                                    String cardId) throws SQLException {

        String sql = "update `creditcard`" +
                " set " +
                "cardPrice = ? ," +
                "cardTime = ? ," +
                "cardState = ? ," +
                "bankId = ? " +
                "where " +
                "userId = ? " +
                "and " +
                "cardId = ? ";
        Object[] params = {cardPrice, cardTime, cardState, bankId, userId, cardId};
        return dbUtil.update(sql, params);
    }

    /**
     * 删除信用卡信息
     *
     * @param userId
     * @param cardId
     * @return
     * @throws SQLException
     */
    public int deleteUserCreditCardById(int userId, String cardId) throws SQLException {
        String sql = "delete from `creditcard` where userId = ? and cardId = ?";
        Object[] params = {userId, cardId};
        return dbUtil.update(sql, params);
    }

    /**
     * 查询符合条件的所有内容
     *
     * @param minTime
     * @param maxTime
     * @param bankId
     * @param isPay
     * @param searchCon
     * @param search
     * @param pageIndex
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getCreditCardAllIsAdmin(String minTime,
                                                             String maxTime,
                                                             String bankId,
                                                             String isPay,
                                                             String searchCon,
                                                             String search,
                                                             int pageIndex) throws SQLException {
        String sql = "select  " +
                "cardId, " +
                "cardNumId, " +
                "cardPrice, " +
                "DATE_FORMAT(cardTime,'%Y-%m-%d') cardTime, " +
                "bankName, " +
                "cardState, " +
                "userTelPhone " +
                "from  " +
                "`creditcard` cd " +
                "LEFT JOIN  " +
                "`user` us " +
                "ON " +
                "cd.userId=us.userId " +
                "LEFT JOIN " +
                "`bank` bk " +
                "ON cd.bankId=bk.bankId " +
                "WHERE ";
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        List<Object> temp = new ArrayList<>();
        if (isString.IsEmpty(search)) {
            sb.append("cardNumId like '%%' ");
        } else {
            if (searchCon.equals("2")) {
                sb.append("us.userTelPhone like '%" + search + "%' ");
            } else {
                sb.append("cd.cardNumId like '%" + search + "%' ");
            }
        }
        if (!isString.IsEmpty(maxTime, minTime)) {
            temp.add(temp.size(), minTime);
            temp.add(temp.size(), maxTime);
            sb.append("and cardTime BETWEEN ? AND ? ");
        }
        if (!isString.IsEmpty(bankId)) {
            temp.add(temp.size(), bankId);
            sb.append("and cd.bankId = ? ");
        }
        if (!isString.IsEmpty(isPay)) {
            temp.add(temp.size(), isPay);
            sb.append("and cardState = ? ");
        }
        sb.append("ORDER BY cardTime asc ");
        sb.append("LIMIT ?,10");
        temp.add(temp.size(), pageIndex);
        Object[] params = temp.toArray();
        logger.info("sql=>" + sb.toString());
        logger.info("params=>" + Arrays.toString(params));
        return dbUtil.queryCustoms(sb.toString(), params);
    }

    /**
     * 获取查询的总数
     *
     * @param minTime
     * @param maxTime
     * @param bankId
     * @param isPay
     * @param searchCon
     * @param search
     * @return
     */
    public int getCreditCardAllCountIsAdmin(String minTime,
                                            String maxTime,
                                            String bankId,
                                            String isPay,
                                            String searchCon,
                                            String search) throws SQLException {
        String sql = "select  " +
                "count(cardId) " +
                "from  " +
                "`creditcard` cd " +
                "LEFT JOIN  " +
                "`user` us " +
                "ON " +
                "cd.userId=us.userId " +
                "WHERE ";
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        List<Object> temp = new ArrayList<>();
        if (search == null || search.length() == 0) {
            sb.append("cardNumId like '%%' ");
        } else {
            if (searchCon.equals("2")) {
                sb.append("us.userTelPhone like '%" + search + "%' ");
            } else {
                sb.append("cd.cardNumId like '%" + search + "%' ");
            }
        }
        if (!isString.IsEmpty(maxTime, minTime)) {
            temp.add(temp.size(), minTime);
            temp.add(temp.size(), maxTime);
            sb.append("and cardTime BETWEEN ? AND ? ");
        }
        if (!isString.IsEmpty(bankId)) {
            temp.add(temp.size(), bankId);
            sb.append("and cd.bankId = ? ");
        }
        if (!isString.IsEmpty(isPay)) {
            temp.add(temp.size(), isPay);
            sb.append("and cardState = ? ");
        }
        Object[] params = temp.toArray();
        logger.info("sql=>" + sb.toString());
        logger.info("params=>" + Arrays.toString(params));
        return dbUtil.queryCount(sb.toString(), params);
    }
}
